﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using Ledict.Utility.Extension;
using Ledict.ConditionExtract.Common;

namespace Ledict.ConditionExtract.DAL
{
    public class ExpressGroupSituationDAL
    {

        private string DbName;

        public ExpressGroupSituationDAL()
        {
            DbName = "JudgeDoc";
        }

        public string GetES_SituationIdsById(string ES_IDs)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetES_SituationIdsById");

            oBase.AddInParameter(oCmd, "@ES_IDs", DbType.String, ES_IDs);

            if (oBase.ExecuteScalar(oCmd) != null)
            {
                return oBase.ExecuteScalar(oCmd).ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ccId"></param>
        /// <param name="esIsMakeUp"></param>
        /// <param name="affirmIdeaResult"></param>
        /// <returns></returns>
        public string GetES_IDsByCCId(string ccId, int? esIsMakeUp, string affirmIdeaResult)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetMS_IDByCCId");
            oBase.AddInParameter(oCmd, "@CC_ID", DbType.String, ccId);
            oBase.AddInParameter(oCmd, "@ES_IsMakeUp", DbType.String, esIsMakeUp.HasValue ? esIsMakeUp.Value.ToString() : string.Empty);
            oBase.AddInParameter(oCmd, "@AffirmIdeaResult", DbType.String, affirmIdeaResult);
            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        }

        /// <summary>
        /// 界面根据构成认定获取单一节点的msid
        /// </summary>
        /// <param name="ccId"></param>
        /// <param name="esIsMakeUp"></param>
        /// <param name="affirmIdeaResult"></param>
        /// <returns></returns>
        public string GetSingleNodeId(string ccId, int? esIsMakeUp, string affirmIdeaResult)
        {
            //string strSql = String.Format(@"select MS_ID from dbo.SYS_Express_MatchSituation
		          //                          where AllStituationIDs='{0}'  and SituationID = '{0}'
		          //                           and AllParentID='-1'  ", ccId);

            string strSql = String.Format(@"select MS_ID from dbo.SYS_Express_MatchSituation
                                      where AllStituationIDs='{0}'  and SituationID = '{0}'
                                       and IsMatch =  {1}
                                      and AffirmIdeaResult={2}  and (AllParentID='-1' or AllParentID=MS_ID )  ", ccId, esIsMakeUp, affirmIdeaResult);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //var obj = oBase.ExecuteScalar(oCmd);
            //if (obj != null && obj != DBNull.Value) return obj.ToString();
            //return string.Empty;
            StringBuilder sb = new StringBuilder();
            var obj = oBase.ExecuteDataSet(oCmd);
            if (obj != null && obj.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in obj.Tables[0].Rows)
                {
                    sb.Append("'" + dr["MS_ID"] + "',");
                }
                return sb.Remove(sb.Length - 1, 1).ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 获取单一节点的msid
        /// </summary>
        /// <param name="ccId"></param>
        /// <returns></returns>
        public string GetSingleNodeMSId(string ccId)
        {
            string strSql = String.Format(@"select MS_ID from dbo.SYS_Express_MatchSituation
		                                    where AllStituationIDs='{0}'  and SituationID = '{0}'
		                                    and AllParentID='-1' ", ccId);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //var obj = oBase.ExecuteScalar(oCmd);
            //if (obj != null && obj != DBNull.Value) return obj.ToString();
            //return string.Empty;
            StringBuilder sb = new StringBuilder();
            var obj = oBase.ExecuteDataSet(oCmd);
            if (obj != null && obj.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in obj.Tables[0].Rows)
                {
                    sb.Append("'" + dr["MS_ID"] + "',");
                }
                return sb.Remove(sb.Length - 1, 1).ToString();
            }
            else
            {
                return "";
            }
        }


        public string getES_IDsByCCId(string ccId,string AffirmIdeaResult) 
        {
            string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
		                                    where (AllStituationIDs='{0}'  and [SituationID] = '{0}')
		                                     and IsMatch =  1
		                                    and AffirmIdeaResult={1}", ccId, AffirmIdeaResult);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            var obj = oBase.ExecuteScalar(oCmd);
            if (obj != null && obj != DBNull.Value) return obj.ToString();
            return string.Empty;
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    return new DataTable();
            //}
        }

        /// <summary>
        /// 刑事用
        /// </summary>
        /// <param name="ccId"></param>
        /// <param name="AffirmIdeaResult"></param>
        /// <returns></returns>
        public string getES_IDsByCCId3(string ccId, int isMatch, string AffirmIdeaResult)
        {
            //string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
            //                          where (AllStituationIDs='{0}'  and [SituationID] = '{0}')
            //                           and IsMatch = {1} and AffirmIdeaResult={2}
            //                         ", ccId,isMatch, AffirmIdeaResult);
            string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
		                                    where [SituationID] = '{0}'
		                                     and IsMatch = {1} and AffirmIdeaResult={2}
		                                   ", ccId, isMatch, AffirmIdeaResult);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            //var obj = oBase.ExecuteScalar(oCmd);
            //if (obj != null && obj != DBNull.Value) return obj.ToString();
            //return string.Empty;
            StringBuilder sb = new StringBuilder();
            DataTable obj = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (obj.Rows.Count > 0)
            {
                for (int i = 0; i < obj.Rows.Count; i++)
                {
                    sb.Append("'"+ obj.Rows[i][0].ToString() + "',");
                }
                return sb.ToString().Substring(0, sb.ToString().Length - 1);
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 民事用
        /// </summary>
        /// <param name="ccId"></param>
        /// <param name="AffirmIdeaResult"></param>
        /// <returns></returns>
        public string getES_IDsByCCId4(string ccId, string AffirmIdeaResult)
        {
            //string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
		          //                          where (AllStituationIDs='{0}'  and [SituationID] = '{0}')
		          //                          and AffirmIdeaResult={1}
		          //                         ", ccId, AffirmIdeaResult);
            string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
		                                    where  [SituationID] = '{0}'
		                                    and AffirmIdeaResult={1}
		                                   ", ccId, AffirmIdeaResult);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            StringBuilder sb = new StringBuilder();
            DataTable  obj = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (obj.Rows.Count > 0)
            {
                for (int i = 0; i < obj.Rows.Count; i++)
                {
                    sb.Append("'"+obj.Rows[i][0].ToString()+"'','");
                }
                return sb.ToString().Remove(sb.Length-3,3);
            }
            else
            {
                return string.Empty;
            }
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    return new DataTable();
            //}
        }


        public string getES_IDsByCCId1(string ccId)
        {
            string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
		                                    where SituationID = '{0}'", ccId);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            var obj = oBase.ExecuteScalar(oCmd);
            if (obj != null && obj != DBNull.Value) return obj.ToString();
            return string.Empty;
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    return new DataTable();
            //}
        }

        public DataTable getES_IDsByCCId2(string ccId, string AffirmIdeaResult)
        {
            string strSql = String.Format(@"select MS_ID from 	dbo.SYS_Express_MatchSituation
		                                    where SituationID = '{0}'  and AffirmIdeaResult={1} ", ccId, AffirmIdeaResult);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            var obj = oBase.ExecuteDataSet(oCmd);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(string ES_SituationIds)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM SYS_Express_GroupSituation where ES_SituationIds=@ES_SituationIds ");


            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@ES_SituationIds", DbType.String, ES_SituationIds);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetALLList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"   
            select c.EW_ID,  EW_Content ,  EW_CaseId , isnull(ParagraphTypeId,150) as ParagraphTypeId ,ES_SituationIds,
            substring( ES_ParentID,2,100) as ES_ParentID  
            from dbo.SYS_Express_GroupSituationWordRel a 
            join dbo.SYS_Express_GroupSituation b
            on a.ES_IDs=b.ES_IDs
            join dbo.SYS_Express_Word c 
            on a.EW_ID=c.EW_ID
        ");
            Database oBase = DatabaseFactory.CreateDatabase("JudgeDocData");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetALLCaseCauseList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"   
            select distinct substring( ES_ParentID,2,100) as ES_ParentID  
            from dbo.SYS_Express_GroupSituationWordRel a 
            join dbo.SYS_Express_GroupSituation b
            on a.ES_IDs=b.ES_IDs
            join dbo.SYS_Express_Word c 
            on a.EW_ID=c.EW_ID 
            where isnull(ES_ParentID,'')<>'' ");
            Database oBase = DatabaseFactory.CreateDatabase("JudgeDocData");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }


        public DataTable GetMatchSituation()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"   
            select SituationID from SYS_Express_MatchSituation 
        ");
            Database oBase = DatabaseFactory.CreateDatabase("JudgeDoc");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        public bool UpdateMatchSituation(string SituationID, string CauseGroupID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"   
           update  SYS_Express_MatchSituation  set CauseGroupID =@CauseGroupID where SituationID=@SituationID
        ");
            Database oBase = DatabaseFactory.CreateDatabase("JudgeDoc");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@SituationID", DbType.String, SituationID);
            oBase.AddInParameter(oCmd, "@CauseGroupID", DbType.String, CauseGroupID);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;

        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetCaseCauseAutoSource()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"   
             select * from  dbo.SYS_CaseCause
 where ','+AllParentId+',' like'%,CC1603231441340012,%'
 and CC_NodeType = 11 
 ");
            Database oBase = DatabaseFactory.CreateDatabase("JudgeDoc");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="parentId">父节点</param>
        /// <param name="ccNodeTypes">节点类型列表</param>
        /// <param name="isHasParentId">查询结果是否包括父节点（默认包括）</param>
        /// <returns></returns>
        public DataTable GetCaseCauseAutoSource(string parentId, IList<int> ccNodeTypes, bool isHasParentId = true)
        {
            StringBuilder strSql = new StringBuilder();
            if (isHasParentId)
            {
                strSql.Append(string.Format(@" select * from  dbo.SYS_CaseCause
                    where CHARINDEX(',{0},',','+AllParentId+',')>0 and CC_NodeType in ({1}) ", parentId, ccNodeTypes.ListToString(",")));
            }
            else
            {
                strSql.Append(string.Format(@" select * from  dbo.SYS_CaseCause
                    where AllParentId like'%,{0},%' and CC_NodeType in ({1}) and CC_Name <> '属性' ", parentId, ccNodeTypes.ListToString(",")));
            }



            Database oBase = DatabaseFactory.CreateDatabase("JudgeDoc");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }


        /// <summary>
        /// 获得批量数据列表
        /// </summary>
        /// <param name="parentId">父节点</param>
        /// <param name="ccNodeTypes">节点类型列表</param>
        /// <param name="isHasParentId">查询结果是否包括父节点（默认包括）</param>
        /// <returns></returns>
        public DataTable GetCaseCauseBatchSource(string parentId, IList<int> ccNodeTypes, bool isHasParentId = true)
        {
            StringBuilder strSql = new StringBuilder();
            if (isHasParentId)
            {
                strSql.Append(string.Format(@" select * from  dbo.SYS_CaseCause
                    where CHARINDEX(',{0},',','+AllParentId+',')>0 and CC_NodeType in ({1}) and CC_ID<>'{0}' union all select * from SYS_CaseCause where CC_ID='{0}'
                 union all select * from  dbo.SYS_CaseCause   where CHARINDEX(',{0},',','+AllParentId+',')>0 and CC_NodeSubType in ({1})", parentId, ccNodeTypes.ListToString(",")));
            }          
            Database oBase = DatabaseFactory.CreateDatabase("JudgeDoc");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }
    }
}
